# -*- encoding: utf-8 -*-
import itchat
from apscheduler.schedulers.background import BackgroundScheduler
import pandas as pd
from sqlalchemy import create_engine


# @itchat.msg_register(itchat.content.TEXT)
# def text_reply(msg):
#     return msg.text


def job(name,client):
    df = pd.read_sql_query('select * from phonetxt where state=0', client)
    for index, row in df.iterrows():
        phone = row['phone']
        txt = row['txt']
        itchat.send(f'收到短信[{phone}]:\n{txt}', toUserName=name)
    if len(df)>0:
        df['id'] = df['id'].astype(str)
        ids = ','.join(df['id'].tolist())
        sql = f'update phonetxt set state=1 where id in ({ids})'
        try:
            with client.connect() as con:
                con.execute(sql)
        except Exception as e:
            print(e)


def get_mysql(user, password, ip, db, port=3306):
    engine_str = f'mysql+pymysql://{user}:{password}@{ip}:{port}/{db}'
    engine = create_engine(engine_str, encoding='utf-8')
    return engine


if __name__ == '__main__':
    client = get_mysql('root', '', '', 'alpha')
    itchat.auto_login(hotReload=True)
    # itchat.auto_login(hotReload=True, enableCmdQR=2)
    name = itchat.search_friends(nickName='皓')[0]['UserName']
    scheduler = BackgroundScheduler()
    scheduler.add_job(job, 'interval', seconds=10,args=[name,client])
    scheduler.start()
    itchat.run()
